import shutil
from flask_jwt_extended import jwt_required, get_jwt
from flask import request, jsonify, send_from_directory, Blueprint
import pandas as pd
from pypinyin import pinyin, Style
import os
import exts

questions_blueprint = Blueprint('questions', __name__)


# 试题导入
@questions_blueprint.route('/import', methods=['POST'])
@jwt_required()
@exts.permission_required('questions', 'C')
def import_questions():
    """上传文件批量上传试题信息"""
    exam_id = request.form.get('exam_id', type=int, default=None)
    subject = request.form.get('subject', type=str, default=None)
    questions = request.files.get('questions', None)
    answers = request.files.get('answers', None)
    info = request.files.get('info', None)

    if not exam_id or not subject or not questions or not answers or not info:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "缺少必要参数或文件"
        }), 400
    if not questions.filename.endswith('.zip') and not answers.filename.endswith('.zip'):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "文件格式错误",
            "detail": "questions 和 answers 文件必须为 zip 格式"
        }), 400
    if not info.filename.endswith('.xlsx'):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "文件格式错误",
            "detail": "info 文件必须为 xlsx 格式"
        }), 400

    subject_pinyin = ''.join([i[0] for i in pinyin(subject, style=Style.NORMAL)])
    # 创建解压缩临时目录
    temp_dir = './static/temp'
    os.makedirs(temp_dir, exist_ok=True)
    # 解压 questions 和 answers 文件，重命名并移动文件
    questions_extract_path = os.path.join(temp_dir, 'questions')
    answers_extract_path = os.path.join(temp_dir, 'answers')
    dest_path = './static/exam-papers'
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        cursor.execute("SELECT exam_name FROM Exam WHERE exam_id = ? UNION "
                       "SELECT exam_name FROM HistoryExam WHERE exam_id = ?", (exam_id, exam_id))
        exam_name = cursor.fetchone()
        if not exam_name:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到该考试"
            }), 404
        dest_path = os.path.join(dest_path, exam_name[0] + str(exam_id))
        # 判断文件夹是否存在
        if os.path.exists(dest_path):
            shutil.rmtree(dest_path)
        # 创建考试试题文件夹
        exts.extract_and_rename_files(questions, questions_extract_path, dest_path, subject_pinyin, '')
        exts.extract_and_rename_files(answers, answers_extract_path, dest_path, subject_pinyin, 'answer')
        # 读取 info 文件
        df = pd.read_excel(info, sheet_name="Sheet1")
        df.columns = range(len(df.columns))
        df.rename(columns={0: 'question_id', 1: 'knowledge_point', 2: 'type', 3: 'question_score', 4: 'answer'},
                  inplace=True)
        df1 = df.copy()
        df1['exam_id'] = exam_id
        df1['subject'] = subject
        df1['question_path'] = ''
        df1['answer_path'] = ''
        df1['answer'] = df1['answer'].fillna('NULL')
        # dest_path 目录下的文件名
        pictures = os.listdir(dest_path)
        picture_op = {picture.split('.')[0]: picture for picture in pictures}
        for index, row in df.iterrows():
            question_temp = str(row['question_id']) + '-' + subject_pinyin
            answer_temp = str(row['question_id']) + '-' + subject_pinyin + '-' + 'answer'
            if question_temp in picture_op.keys():
                df1.loc[index, 'question_path'] = picture_op[question_temp]
            else:
                df1.loc[index, 'question_path'] = 'NULL'
            if answer_temp in picture_op.keys():
                df1.loc[index, 'answer_path'] = picture_op[answer_temp]
            else:
                df1.loc[index, 'answer_path'] = 'NULL'
            cursor.execute("SELECT knowledgepoint_id FROM KnowledgePoint WHERE code_id = ?",
                           (row['knowledge_point'],))
            knowledge_point_id = cursor.fetchone()
            if knowledge_point_id:
                df1.loc[index, 'knowledge_point'] = knowledge_point_id[0]
        # 插入数据库
        cursor.executemany("INSERT INTO ExamPaper (exam_id, subject, question_id, knowledge_point, type,"
                           " question_score, question_path, answer_path, answer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                           df1[['exam_id', 'subject', 'question_id', 'knowledge_point', 'type', 'question_score',
                                'question_path', 'answer_path', 'answer']].values.tolist())
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "试题导入成功",
            "detail": "共导入 {} 条试题".format(cursor.rowcount)
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "detail": str(e)
        }), 500
    finally:
        if conn:
            conn.close()
        shutil.rmtree(temp_dir)


@questions_blueprint.route('/update/<int:id_>', methods=['PUT'])
@jwt_required()
@exts.permission_required('questions', 'U')
def update_question(id_):
    """更新试题信息"""
    valid_params = ['exam_id', 'subject', 'question_id', 'knowledge_point', 'type', 'question_score', 'answer']
    required_params = ['exam_id', 'subject', 'question_id', 'question_score']
    data = {key: request.form.get(key) for key in valid_params}
    data["question_picture"] = request.files.get('question_picture', None)
    data["answer_picture"] = request.files.get('answer_picture', None)

    if not data["question_picture"]:
        data.pop("question_picture")
    if not data["answer_picture"]:
        data.pop("answer_picture")

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.form:
            data.pop(key)

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.form and not request.form[key]:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ExamPaper WHERE id = ?", (id_,))
        exam_paper = cursor.fetchone()
        if not exam_paper:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到该试题"
            }), 404

        if 'question_picture' in data or 'answer_picture' in data:
            cursor.execute("SELECT exam_name FROM Exam WHERE exam_id = ? UNION "
                           "SELECT exam_name FROM HistoryExam WHERE exam_id = ?", (exam_paper['exam_id'],
                                                                                   exam_paper['exam_id']))
            exam_name = cursor.fetchone()
            if not exam_name:
                return jsonify({
                    "code": 404,
                    "status": "error",
                    "message": "未找到该考试"
                }), 404

            dest_path = './static/exam-papers/' + exam_name[0] + str(exam_paper['exam_id'])
            os.makedirs(dest_path, exist_ok=True)
            subject_pinyin = ''.join([i[0] for i in pinyin(exam_paper['subject'], style=Style.NORMAL)])
            # 删除原有图片, 如果存在;并将新图片移动到目标文件夹
            if 'question_picture' in data:
                if exam_paper["question_path"] and exam_paper["question_path"] != 'NULL' and \
                        os.path.exists(os.path.join(dest_path, exam_paper["question_path"])):
                    os.remove(os.path.join(dest_path, exam_paper["question_path"]))
                question_ext = os.path.splitext(data['question_picture'].filename)[1]
                new_question_filename = f"{exam_paper['question_id']}-{subject_pinyin}{question_ext}"
                data['question_picture'].save(os.path.join(dest_path, new_question_filename))
                data['question_path'] = new_question_filename
                data.pop('question_picture')

            if 'answer_picture' in data:
                if exam_paper["answer_path"] and exam_paper["answer_path"] != 'NULL' and \
                        os.path.exists(os.path.join(dest_path, exam_paper["answer_path"])):
                    os.remove(os.path.join(dest_path, exam_paper["answer_path"]))
                answer_ext = os.path.splitext(data['answer_picture'].filename)[1]
                new_answer_filename = f"{exam_paper['question_id']}-{subject_pinyin}-answer{answer_ext}"
                data['answer_picture'].save(os.path.join(dest_path, new_answer_filename))
                data['answer_path'] = new_answer_filename
                data.pop('answer_picture')
        # 更新数据库
        update_query = "UPDATE ExamPaper SET " + ', '.join([f"{key} = ?" for key in data.keys()]) + " WHERE id = ?"
        cursor.execute(update_query, list(data.values()) + [id_])
        conn.commit()

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "试题信息更新成功",
            "data": {
                "id": id_
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 试题删除
@questions_blueprint.route('/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('questions', 'D')
def delete_question():
    """删除试题信息"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的试题 ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        results = []
        for item in data:
            cursor.execute("SELECT * FROM ExamPaper WHERE id = ?", (item,))
            exam_paper = cursor.fetchone()
            if not exam_paper:
                results.append({
                    "id": item,
                    "status": "error",
                    "message": "未找到该试题"
                })
                continue
            cursor.execute("SELECT exam_name FROM Exam WHERE exam_id = ? UNION "
                           "SELECT exam_name FROM HistoryExam WHERE exam_id = ?", (exam_paper['exam_id'],
                                                                                   exam_paper['exam_id']))
            exam_name = cursor.fetchone()
            if not exam_name:
                results.append({
                    "id": item,
                    "status": "error",
                    "message": "未找到该考试"
                })
                continue
            dest_path = './static/exam-papers/' + exam_name[0] + str(exam_paper['exam_id'])
            if exam_paper["question_path"] and exam_paper["question_path"] != 'NULL' and \
                    os.path.exists(os.path.join(dest_path, exam_paper["question_path"])):
                os.remove(os.path.join(dest_path, exam_paper["question_path"]))
            if exam_paper["answer_path"] and exam_paper["answer_path"] != 'NULL' and \
                    os.path.exists(os.path.join(dest_path, exam_paper["answer_path"])):
                os.remove(os.path.join(dest_path, exam_paper["answer_path"]))
            cursor.execute("DELETE FROM ExamPaper WHERE id = ?", (item,))
            results.append({
                "id": item,
                "status": "success",
                "message": "试题删除成功"
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": results
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 试题查询
@questions_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('questions', 'R')
def query_question():
    """查询试题信息"""
    school_id = request.args.get('school_id', type=int, default=None)
    exam_name = request.args.get('exam_name', type=str, default=None)
    exam_id = request.args.get('exam_id', type=int, default=None)
    subject = request.args.get('subject', type=str, default=None)
    knowledge_point = request.args.get('knowledge_point', type=int, default=None)
    question_type = request.args.get('question_type', type=str, default=None)
    question_score = request.args.get('question_score', type=int, default=None)
    student_grade = request.args.get('student_grade', type=str, default=None)
    grade = request.args.get('grade', type=str, default=None)
    year = request.args.get('year', type=str, default=None)
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)

    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须大于 0"
        }), 400

    offset = (page - 1) * page_size

    # 构建查询条件
    conditions = []
    if school_id:
        conditions.append(f"e.school_id = {school_id}")
    if exam_name:
        conditions.append(f"e.exam_name LIKE '%{exam_name}%'")
    if exam_id:
        conditions.append(f"e.exam_id = {exam_id}")
    if subject:
        conditions.append(f"ep.subject = '{subject}'")
    if knowledge_point:
        conditions.append(f"ep.knowledge_point = {knowledge_point}")
    if question_type:
        conditions.append(f"ep.type = '{question_type}'")
    if question_score:
        conditions.append(f"ep.question_score = {question_score}")
    if student_grade:
        conditions.append(f"e.student_grade LIKE '%{student_grade}%'")
    if grade:
        conditions.append(f"e.grade LIKE '%{grade}%'")
    if year:
        conditions.append(f"e.year LIKE '%{year}%'")

    where_clause = " AND ".join(conditions) if conditions else "1=1"

    # 构建联表查询语句
    sql = f"""
        SELECT DISTINCT ep.*, 
               COALESCE(e.exam_name, he.exam_name) AS exam_name, 
               COALESCE(e.year, he.year) AS year, 
               COALESCE(e.date, he.date) AS date, 
               s.school_name, 
               kp.description
        FROM ExamPaper ep
        LEFT JOIN Exam e ON ep.exam_id = e.exam_id
        LEFT JOIN HistoryExam he ON ep.exam_id = he.exam_id
        LEFT JOIN School s ON COALESCE(e.school_id, he.school_id) = s.school_id
        LEFT JOIN KnowledgePoint kp ON ep.knowledge_point = kp.knowledgepoint_id
        WHERE {where_clause}
        ORDER BY ep.id DESC LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(DISTINCT id) AS total FROM (
            SELECT DISTINCT ep.id
            FROM ExamPaper ep
            LEFT JOIN Exam e ON ep.exam_id = e.exam_id
            LEFT JOIN HistoryExam he ON ep.exam_id = he.exam_id
            LEFT JOIN School s ON COALESCE(e.school_id, he.school_id) = s.school_id
            LEFT JOIN KnowledgePoint kp ON ep.knowledge_point = kp.knowledgepoint_id
            WHERE {where_clause}
        ) AS subquery
    """

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(sql_count)
        total_records = cursor.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询当前页数据
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的试题信息"
            }), 404
        results = [dict(zip([column[0] for column in cursor.description], row)) for row in results]

        base_url = exts.DOMAIN
        for row in results:
            question_path = row.get('question_path')
            answer_path = row.get('answer_path')
            if question_path and question_path != 'NULL':
                row['question_path'] = base_url + f"static/exam-papers/{row['exam_name']}{row['exam_id']}/{question_path}"
            if answer_path and answer_path != 'NULL':
                row['answer_path'] = base_url + f"static/exam-papers/{row['exam_name']}{row['exam_id']}/{answer_path}"

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 获取试题详细信息
@questions_blueprint.route('/info/<int:id_>', methods=['GET'])
@jwt_required()
@exts.permission_required('questions', 'R')
def get_question_info(id_):
    """获取试题详细信息"""
    if id_ is None:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "id 参数不能为空"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        sql = f"""
            SELECT DISTINCT ep.*,
                     COALESCE(e.exam_name, he.exam_name) AS exam_name,
                     COALESCE(e.year, he.year) AS year,
                     COALESCE(e.date, he.date) AS date,
                     s.school_name,
                     kp.description
            FROM ExamPaper ep
            LEFT JOIN Exam e ON ep.exam_id = e.exam_id
            LEFT JOIN HistoryExam he ON ep.exam_id = he.exam_id
            LEFT JOIN School s ON COALESCE(e.school_id, he.school_id) = s.school_id
            LEFT JOIN KnowledgePoint kp ON ep.knowledge_point = kp.knowledgepoint_id
            WHERE ep.id = {id_}
        """
        cursor.execute(sql)
        result = cursor.fetchone()
        if not result:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到该试题"
            }), 404

        columns = [column[0] for column in cursor.description]
        result = dict(zip(columns, result))

        base_url = exts.DOMAIN
        question_path = result['question_path']
        answer_path = result['answer_path']
        if question_path != 'NULL':
            result[
                'question_path'] = base_url + f"static/exam-papers/{result['exam_name']}{result['exam_id']}/{question_path}"
        if answer_path != 'NULL':
            result[
                'answer_path'] = base_url + f"static/exam-papers/{result['exam_name']}{result['exam_id']}/{answer_path}"
        # 获取班级答题情况
        cursor.execute("SELECT * FROM ClassMistakes WHERE exampaper_id = ?", (id_,))
        info = cursor.fetchall()
        info = [dict(zip([column[0] for column in cursor.description], row)) for row in info]
        correct_answer_rate_list = []
        # 计算班级的正答率
        for item in info:
            question_classerrorcount = item['question_classerrorcount']
            question_classallcount = item['question_classallcount']
            correct_answer_rate = round(((question_classallcount - question_classerrorcount) / question_classallcount)
                                        * 100, 2)
            correct_answer_rate_list.append({
                "class": item['class'],
                "class_score": item['question_classcores'],
                "school_score": item['question_schoolscores'],
                "class_error_count": question_classerrorcount,
                "class_all_count": question_classallcount,
                "correct_answer_rate": str(correct_answer_rate) + '%'
            })
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "question": result,
                "info": correct_answer_rate_list,
            }
        }), 200

    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 上传文件批量上传学生答题信息
@questions_blueprint.route('/scores/import', methods=['POST'])
@jwt_required()
@exts.permission_required('questions', 'C')
def import_scores():
    """上传文件批量上传学生答题信息"""
    exam_id = request.form.get('exam_id', type=int, default=None)
    subject = request.form.get('subject', type=str, default=None)
    examtype = request.form.get('examtype', type=int, default=None)
    show = request.form.get('show', type=int, default=1)
    file = request.files.get('file', None)

    if not exam_id or not subject or not examtype or not show or not file:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "缺少必要参数或文件"
        }), 400
    if not file.filename.endswith('.xlsx'):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "文件格式错误",
            "detail": "文件必须为 xlsx 格式"
        }), 400

    df = pd.read_excel(file)
    df.columns = range(len(df.columns))
    df.rename(columns={0: 'student_id', 1: 'class', 2: 'objective_qscores', 3: 'subjective_qscores'}, inplace=True)
    df.iloc[:, 2:] = df.iloc[:, 2:].fillna(0)
    # 将答题情况拼接成字符串, 以 - 分隔, 分数全部转化为整数
    df['question_scores'] = df.iloc[:, 4:].apply(lambda x: '-'.join([str(int(i)) for i in x]), axis=1)
    df['exam_id'] = exam_id
    df['subject'] = subject
    df['examtype'] = examtype
    df['show'] = show
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查找考试信息
        cursor.execute("SELECT * FROM Exam WHERE exam_id = ?", (exam_id,))
        exam_info = cursor.fetchone()
        if not exam_info:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到该考试"
            }), 404
        df['school_id'] = exam_info['school_id']
        df['year'] = exam_info['year']
        df['student_grade'] = exam_info['student_grade']
        # 插入数据库
        cursor.executemany("INSERT INTO AllstudentQuestionScore (student_id, class, objective_qscores, "
                           "subjective_qscores,"
                           " question_scores, exam_id, subject, examtype, show, school_id, year, student_grade)"
                           " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
                           df[['student_id', 'class', 'objective_qscores', 'subjective_qscores', 'question_scores',
                               'exam_id', 'subject', 'examtype', 'show', 'school_id', 'year', 'student_grade']].values
                           .tolist())
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "学生答题信息导入成功",
            "detail": "共导入 {} 条学生答题信息".format(cursor.rowcount)
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "detail": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 上传模板下载
@questions_blueprint.route('/download/question-template/<int:id_>', methods=['GET'])
@jwt_required()
@exts.permission_required('questions', 'R')
def download_question_template(id_):
    """下载试题上传模板"""
    template_filenames = {
        1: "试题上传模板（通用）.xlsx",
        2: "小题分上传模板（通用）.xlsx",
    }
    template_filename = template_filenames.get(id_)
    if not template_filename:
        # 如果提供的代码不在映射表中，返回错误信息
        return jsonify({
            "code": 400,
            "details": "提供的模板代码无效",
            "message": "请求参数错误",
            "status": "error"
        }), 400
    template_path = os.path.join("static/upload-templates/question-templates", template_filename)
    if not os.path.exists(template_path):
        # 如果文件不存在，返回错误信息
        return jsonify({
            "code": 404,
            "details": f"模板文件'{template_filename}'不存在",
            "message": "请求参数错误",
            "status": "error"
        }), 404
    try:
        return send_from_directory("static/upload-templates/question-templates", template_filename, as_attachment=True)
    except Exception as e:
        return jsonify({
            "code": 500,
            "details": str(e),
            "message": "下载模板时发生错误",
            "status": "error"
        }), 500

